---
title: "SQL-PLOC-03: Provider locations by provider type"
summary: Retrieve all locations having a given provider type code
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PLOC-03 Provider locations by provider type

Retrieves every `PROVIDER_LOCATION` record matching a given provider type code, used to filter
a `clsProviderLocationCollection` by `ProviderTypeCode`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PLOC-03 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderLocationCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderTypeCode` | Input | String | 5 | `PROVIDER_LOCATION.PROVIDER_TYPE_CODE` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | SELECT | `APPROVED_FLAG`, `APPROVED_USER`, `BUSINESS_FLAG`, `CRITICAL_ACCESS_HOSPITAL_FLAG`, `BUSINESS_NAME`, `FAX_NUM`, `FIRST_NAME`, `INELIGIBILITY_FLAG`, `LAST_NAME`, `MIDDLE_NAME`, `PAYEE_NAME`, `PHONE_AREA_CODE`, `PHONE_NUM`, `PHONE_NUM_EXT`, `POSSIBLE_FRAUD_FLAG`, `PRIMARY_LOCATION_FLAG`, `PROVIDER_LOCATION_SEQ_NUM`, `PROVIDER_SEQ_NUM`, `PROVIDER_TYPE_CODE`, `RECENT_ACTIVITY_FLAG`, `TOLL_FREE_FAX_NUM`, `TOLL_FREE_PHONE_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER`, `WEB_SITE_ADDRESS`, `EMAIL_ADDR` |

## Sample SQL

```sql
-- Provider locations by provider type
SELECT
  pl.APPROVED_FLAG                 AS IsApproved,
  pl.APPROVED_USER                 AS ApprovedUser,
  pl.BUSINESS_FLAG                 AS IsBusiness,
  pl.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  pl.BUSINESS_NAME                 AS BusinessName,
  pl.FAX_NUM                       AS FaxNum,
  pl.FIRST_NAME                    AS FirstName,
  pl.INELIGIBILITY_FLAG            AS IsIneligible,
  pl.LAST_NAME                     AS LastName,
  pl.MIDDLE_NAME                   AS MiddleName,
  pl.PAYEE_NAME                    AS PayeeName,
  pl.PHONE_AREA_CODE               AS PhoneAreaCode,
  pl.PHONE_NUM                     AS PhoneNum,
  pl.PHONE_NUM_EXT                 AS PhoneNumExt,
  pl.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  pl.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  pl.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  pl.PROVIDER_SEQ_NUM              AS ProviderID,
  pl.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  pl.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  pl.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  pl.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  pl.UPDATE_DATE                   AS UpdateDate,
  pl.UPDATE_NUM                    AS UpdateNum,
  pl.UPDATE_USER                   AS UpdateUser,
  pl.WEB_SITE_ADDRESS              AS WebSiteAddress,
  pl.EMAIL_ADDR                    AS EmailAddress
FROM PROVIDER_LOCATION pl
WHERE PROVIDER_TYPE_CODE = :ProviderTypeCode
```

## Operations and processes
- [Retrieve provider locations by provider or type](../operations/retrieve-provider-locations-by-provider-or-type.md)

## Business rules
- [BR-PLOC-004: Ineligibility period exclusion](../business-rules.md#br-ploc-004-ineligible-locations-are-excluded-from-filtered-lists)

## Sources
- SAIF.Business.View.Provider/clsProviderLocationCollection.vb